USE [InterestProjection]
GO

/****** Object:  StoredProcedure [dbo].[GetLoansDetailedInterestProjectionAddon]    Script Date: 09/05/2013 17:17:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[GetLoansDetailedInterestProjectionAddon]
(
	@from_report_date DATETIME,
	@to_report_date DATETIME,
	@interest DECIMAL(18, 2),
	@page INT = NULL,
	@recs_per_page INT = NULL
)
AS
BEGIN


	DECLARE @sql_query NVARCHAR(MAX), @num_months INT, @month INT, @year INT, @count INT,
			@report_month_header VARCHAR(16), @first_rec INT, @last_rec INT			
	
	
	IF @page IS NOT NULL AND @page > 0 AND @recs_per_page IS NOT NULL AND @recs_per_page > 0 --optional parameters supplied, therefore, paging required
	BEGIN
		SET @first_rec = (@page - 1) * @recs_per_page
		SET @last_rec = (@page * @recs_per_page + 1);
	END
	
	SET @num_months = DATEDIFF(MONTH, @from_report_date, @to_report_date)
	SET @month = MONTH(@from_report_date)
	SET @year = YEAR(@from_report_date)
	SET @count = 0
	SET @sql_query = ''

	/*set report date: @from = 1st day of the month and @to = last day*/
	SET @from_report_date = dbo.DateFromParts --end of the the month for which the report is required
	(
		YEAR(@from_report_date), 
		MONTH(@from_report_date), 
		1--first day of the month
	)
	SET @to_report_date = dbo.DateFromParts --end of the the month for which the report is required
	(
		YEAR(@to_report_date), 
		MONTH(@to_report_date), 
		DAY(DATEADD(mm, DATEDIFF(mm, -1, dbo.DateFromParts(YEAR(@to_report_date), MONTH(@to_report_date), 1)), -1))
	)
	
	IF @page IS NOT NULL AND @page > 0 AND @recs_per_page IS NOT NULL AND @recs_per_page > 0--optional parameters supplied, therefore, paging required
	BEGIN
		SET @sql_query = '
			WITH temp_result as
			(	'			
	END
	
	SET @sql_query = @sql_query + '
			SELECT 
				ROW_NUMBER() OVER(ORDER BY start_date) as RowNum,
				l.loan_no AS [Loan No],
				CONVERT(VARCHAR(9), l.start_date, 6) AS [Start Date],
				CONVERT(VARCHAR(9), l.end_date, 6) AS [End Date],
				l.principal AS [Amount],
				l.term AS [Term],
				l.period AS [Frequency],'
	
	WHILE @count <= @num_months
	BEGIN
		IF @month <= 12
		BEGIN
			SET @report_month_header = 
			(		
				CASE 
					WHEN @month = 1 THEN 'January'
					WHEN @month = 2 THEN 'February'
					WHEN @month = 3 THEN 'March'
					WHEN @month = 4 THEN 'April'
					WHEN @month = 5 THEN 'May'
					WHEN @month = 6 THEN 'June'
					WHEN @month = 7 THEN 'July'
					WHEN @month = 8 THEN 'August'
					WHEN @month = 9 THEN 'September'
					WHEN @month = 10 THEN 'October'
					WHEN @month = 11 THEN 'November'
					WHEN @month = 12 THEN 'December'
				END 
			) + ' ' + CONVERT(VARCHAR(4), @year)
			
			SET @sql_query = @sql_query + '
			dbo.CalInterestAddon(' + CAST(@month AS VARCHAR(2)) + ',' + CAST(@year AS VARCHAR(4)) +', ' + CAST(@interest AS VARCHAR(4)) + ', l.start_date, l.end_date, l.principal, l.period) as [' + @report_month_header+ ']'
			
			/*check if a ',' is required*/
			IF @count <> @num_months
			BEGIN
				SET @sql_query = @sql_query + ','
			END
			/*perform increments*/
			SET @month = @month + 1
			SET @count = @count + 1
		END
		ELSE
		BEGIN
			SET @month = 1
			SET @year = @year + 1
		END
	END
	
	SET @sql_query = @sql_query + ' 
		FROM 
			loan_addon AS l
		WHERE
			l.end_date >= '''+ CONVERT(varchar(10), @from_report_date, 126) + ''' AND l.start_date <= ''' + CONVERT(varchar(10), @to_report_date, 126) +'''' 	
	
	IF @page IS NOT NULL AND @page > 0 AND @recs_per_page IS NOT NULL AND @recs_per_page > 0 --optional parameters supplied, therefore, paging required
	BEGIN
		SET @sql_query = @sql_query + ' 
		)
		SELECT 
			TOP ' + CAST((@last_rec-1) AS VARCHAR(10)) + ' *
		FROM 
			temp_result
		WHERE 
			RowNum > ' + CAST(@first_rec AS VARCHAR(10)) + ' AND RowNum < ' + CAST(@last_rec AS VARCHAR(10))
	END
		
	EXECUTE(@sql_query)	
END

GO


